package com.dp.service.hdnewsmanage;

import java.io.IOException;
import java.io.Serializable;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.concurrent.Executor;
import java.util.concurrent.Executors;

import javax.persistence.Query;

import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import com.dp.pojo.CommentContent;
import com.dp.pojo.JsonPage;
import com.dp.pojo.NewsVo;
import com.dp.pub.SysProperty;
import com.dp.service.impl3.BaseImpl;
import com.dp.util.BookConfig;
import com.dp.util.DataMap;
import com.dp.util.GenerateNewsHtml;
import com.dp.util.ImageScalee;
import com.dp.vo.NewsContenttitle;
import com.dp.vo.NewsResources;
import com.dp.vo.TAreainfo;
import com.dp.vo.TChannels;
import com.dp.vo.THdContents;
import com.dp.vo.THdTags;
import com.dp.vo.THdTagsHdContents;

@Service
@Transactional
public class HdnewsServiceImpl extends BaseImpl {
	
	private static int HDNEWS_TYPE = 2;
	/**
	 * 获得所有手机报内容信息总记录数
	 * @param hashMap
	 * @return Integer
	 */
	public Integer findAllHdNewsEntityCount(DataMap hashMap){
		
		NewsVo news=(NewsVo) hashMap.get("news");
		Object s_role_id = hashMap.get("role_id");
		Object s_user_id = hashMap.get("user_id");
		String userids = (String)hashMap.get("userids");
		Integer iCount = 0; 
		try {
//			this.init();
			StringBuffer sqlS =new StringBuffer("select count(nc.cid) from t_hdcontents tc,news_contenttitle nc ");
			if(news!=null && news.getChannelId()!=null&& news.getChannelId().toString().trim().length() > 0) {
				sqlS.append(",news_extends_channel nec,news_extends_channel_title nect ");
				sqlS.append(" where nec.channel_id='"+news.getChannelId()+"' and nec.channleid=nect.channleid and nc.cid=nect.cid ");
				sqlS.append(" and nc.contentid=tc.contentid");
			}
			else
				sqlS.append(" where nc.contentid=tc.contentid ");
			if(s_role_id!=null&&!"".equals(s_role_id)) {
				sqlS.append(" and tc.user_id = '"+ s_user_id +"'");
			}else if(userids!=null){
				sqlS.append(" and tc.user_id in ("+ userids +")");
			}
			
			if(news!=null){
				if(news.getContentName()!=null&& news.getContentName().toString().trim().length() > 0){
					sqlS.append(" AND tc.contenttitle like '%"+news.getContentName().toString().trim() + "%'");
				}
				if(news.getContentStatus()!=null&& news.getContentStatus().toString().trim().length() > 0){
					sqlS.append(" AND tc.content_status='"+news.getContentStatus().toString().trim() + "'");
				}
				if(news.getArea_county()!=null){
					sqlS.append(" AND tc.area_county="+news.getArea_county() + "");
				}else if(news.getArea_city()!=null){
					sqlS.append(" AND tc.area_city="+news.getArea_city() + "");
				}else if(news.getArea_province()!=null){
					sqlS.append(" AND tc.area_province="+news.getArea_province() + "");
				}
			}
			logger.info("findAllHdNewsEntityCount excute sql: " + sqlS.toString());
//			this.rs1=stmt.executeQuery(sqlS.toString());
//			if(this.rs1.next()){
//				iCount=rs1.getInt(1);
//			}
//			this.closeAll();
			sql=sqlS.toString();
			iCount=this.getOneInt();
		} catch (Exception e) {
			logger.error("findAllHdNewsEntityCount fail !!!" + e.getMessage());
			e.printStackTrace();
		} finally {
//			this.closeAll();
		}
		return iCount;
	}
	
	/**
	 * 获得所有手机报内容信息
	 * @param hashMap
	 * @param firstrow
	 * @param maxrow
	 * @return List<NewsVo>
	 */
	public List<NewsVo> findAllHdNewsEntity(DataMap hashMap, Integer firstrow,Integer maxrow){
		JsonPage jpage=(JsonPage) hashMap.get("jpage");
		NewsVo news=(NewsVo) hashMap.get("news");
		List<NewsVo> newsList = null; 
		Object s_role_id = hashMap.get("role_id");
		Object s_user_id = hashMap.get("user_id");
		String userids = (String)hashMap.get("userids");
		try {
//			this.init();
			StringBuffer sqlS =new StringBuffer("select tc.contentid,nc.cid,tc.contenttitle,tc.content_status,tc.user_id," +
			" tc.updatetime,tc.cause,tc.area_street,tc.address,tc.phone,tc.price,tc.trade_time,tc.park,tc.card,tc.isCommentAudit " +
			" from t_hdcontents tc,news_contenttitle nc ");
			if(news!=null && news.getChannelId()!=null&& news.getChannelId().toString().trim().length() > 0) {
				sqlS.append(",news_extends_channel nec,news_extends_channel_title nect ");
				sqlS.append(" where nec.channel_id='"+news.getChannelId()+"' and nec.channleid=nect.channleid and nc.cid=nect.cid ");
				sqlS.append(" and nc.contentid=tc.contentid");
			}
			else
				sqlS.append(" where nc.contentid=tc.contentid ");
			if(s_role_id!=null&&!"".equals(s_role_id)) {
				sqlS.append(" and tc.user_id = '"+ s_user_id +"'");
			}else if(userids!=null){
				sqlS.append(" and tc.user_id in ("+ userids +")");
			}
			
			if(news!=null){
				if(news.getContentName()!=null&& news.getContentName().toString().trim().length() > 0){
					sqlS.append(" AND tc.contenttitle like '%" + news.getContentName().toString().trim() + "%'");
				}
				if(news.getContentStatus()!=null&& news.getContentStatus().toString().trim().length() > 0){
					sqlS.append(" AND tc.content_status='" + news.getContentStatus().toString().trim() + "'");
				}
				if(news.getArea_county()!=null){
					sqlS.append(" AND tc.area_county="+news.getArea_county() + "");
				}else if(news.getArea_city()!=null){
					sqlS.append(" AND tc.area_city="+news.getArea_city() + "");
				}else if(news.getArea_province()!=null){
					sqlS.append(" AND tc.area_province="+news.getArea_province() + "");
				}
			}
			
			if(jpage!=null){
				if(jpage.getSidx()!=null&&!"".equals(jpage.getSidx())){
					sqlS.append(" order by "+jpage.getSidx()+" "+jpage.getSord()+"");
				}
			}
			if(firstrow!=null&&maxrow!=null){
				sqlS.append(" limit "+firstrow+","+maxrow+"");
			}
			logger.info("findAllHdNewsEntity excute dql: " + sqlS.toString());
//			ArrayList conList = this.queryALLResultSet(sqlS.toString());
			newsList=new ArrayList<NewsVo>();
			NewsVo newsVo=null;
			SimpleDateFormat fmt_temp = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
//			if(conList.size()>0)
//			{
//				for(int i=0;i<conList.size();i++)
//				{
//					HashMap tmp = (HashMap)conList.get(i);
			List<Object[]> temp=this.em.createNativeQuery(sql).getResultList();
			for(Object[]o:temp)
			{
//				"select tc.contentid,nc.cid,tc.contenttitle,tc.content_status,tc.user_id," +
//				" tc.updatetime,tc.cause,tc.area_street,tc.address,tc.phone,tc.price,tc.trade_time,tc.park,tc.card,tc.isCommentAudit " 
					newsVo=new NewsVo();
//					newsVo.setContentId((String)tmp.get("contentid"));
//					newsVo.setCid(Integer.parseInt((String)tmp.get("cid")));
//					newsVo.setContentName((String)tmp.get("contenttitle"));
//					newsVo.setContentStatus((String)tmp.get("content_status"));
//					newsVo.setUserId((String)tmp.get("user_id"));
//					newsVo.setLastupdatetime(fmt_temp.parse((String)tmp.get("updatetime")));
//					newsVo.setCause((String)tmp.get("cause"));
					newsVo.setContentId(this.getString(o[0]));
					newsVo.setCid(this.getInteger(o[1]));
					newsVo.setContentName(this.getString(o[2]));
					newsVo.setContentStatus(this.getString(o[3]));
					newsVo.setUserId(this.getString(o[4]));
					newsVo.setLastupdatetime(fmt_temp.parse(this.getString(o[5])));
					newsVo.setCause(this.getString(o[6]));
					sql="select sum(nr.size) sizenr from news_contenttitle nc,news_resources nr,news_contenresource ncr"
						+" where nc.cid="+newsVo.getCid()+" and nc.cid=ncr.cid and nr.rid=ncr.rid group by ncr.cid";
					String sizestr = this.getOneString();
					int size = 0;
					if(sizestr != null && !"".equals(sizestr))
					{
						size = Integer.parseInt(sizestr);
						newsVo.setAttachSize(size/1024+" K");
					}
					/*newsVo.setArea_street(Integer.parseInt((String)tmp.get("area_street")));
					newsVo.setAddress((String)tmp.get("address"));
					newsVo.setPhone((String)tmp.get("phone"));
					newsVo.setPrice((String)tmp.get("price"));
					newsVo.setTrade_time((String)tmp.get("trade_time"));
					newsVo.setPark(Integer.parseInt((String)tmp.get("park")));
					newsVo.setCard(Integer.parseInt((String)tmp.get("card")));
					newsVo.setIsCommentAudit(Integer.parseInt((String)tmp.get("isCommentAudit")));*/
					newsList.add(newsVo);
				}
			
//			this.closeAll();
		} catch (Exception e) {
			logger.error("findAllHdNewsEntity fail !!!" + e.getMessage());
			e.printStackTrace();
		} finally {
//			this.closeAll();
		}
		return newsList;
	}
	
	public NewsVo getHdNewsByCOntentid(String contentid)
	{
		NewsVo tmp = null;
		this.sql = "SELECT n FROM THdContents n where n.contentid=:contentid";
		try {
			Query query = em.createQuery(sql);
			query.setParameter("contentid", contentid);
			THdContents s = (THdContents) query.getSingleResult();
			if(s != null)
			{
				tmp = new NewsVo();
				tmp.setCid(((NewsContenttitle)s.getNewsContenttitleSet().toArray()[0]).getCid());
				tmp.setContentId(s.getContentid());
				tmp.setContentName(s.getContenttitle());
				tmp.setContentSummary(s.getContentsummary());
				tmp.setCategory(s.getCategory());
				tmp.setArea(s.getArea());
				tmp.setArea_province(s.getArea_province());
				tmp.setArea_city(s.getArea_city());
				tmp.setArea_county(s.getArea_county());
				tmp.setNote(s.getNote());
				tmp.setKeyword(s.getKeyword());
				tmp.setIconurl(s.getIconurl());
				tmp.setContentHtml(s.getHtml());
				tmp.setLastupdatetime(s.getUpdatetime());
				tmp.setUserId(s.getUserId());
				tmp.setContentStatus(s.getContentStatus());
				tmp.setCause(s.getCause());
				
				tmp.setArea_street(s.getArea_street());
				tmp.setAddress(s.getAddress());
				tmp.setPhone(s.getPhone());
				tmp.setPrice(s.getPrice());
				tmp.setTrade_time(s.getTrade_time());
				tmp.setPark(s.getPark());
				tmp.setCard(s.getCard());
				tmp.setIsCommentAudit(s.getIsCommentAudit());
			}
		} catch (Exception e) {
			log.error(" getHdNewsByCOntentid fail !!!" +e.getMessage());
			e.printStackTrace();
		}
		return tmp;
	}
	
	public int getResSize(String contentid)
	{
		String sql = "SELECT sum(size) FROM news_resources nr,news_contenresource ncr,news_contenttitle nc " +
				"where nc.contentid='"+contentid+"' and ncr.cid=nc.cid and nr.rid=ncr.rid";
		try {
			log.info(" getResSize excute sql:" +sql);
		   return	this.getOneInt()/1024;
//			String size = this.queryOne(sql);
//			if(size == null)
//				return 0;
//			return Integer.parseInt(size)/1024;
		} catch (Exception e) {
			log.info(" getResSize fail !!!" +e.getMessage());
			e.printStackTrace();
			return 0;
		}
	}
	
	public String addHdNews(NewsVo news) 
	{
		String cid=null;
		String contentid = this.ObjectToStringDB(news.getContentId());
		String contentname = this.ObjectToStringDB(news.getContentName());
		String contentsum = this.ObjectToStringDB(news.getContentSummary());
		String cate = this.ObjectToStringDB(news.getCategory());
		String area = this.ObjectToStringDB(news.getArea());
		String note = this.ObjectToStringDB(news.getNote());
		String keyword = this.ObjectToStringDB(news.getKeyword());
		String iconurl = this.ObjectToStringDB(news.getIconurl());
		String html = this.ObjectToStringDB(news.getContentHtml());
		String userid = this.ObjectToStringDB(news.getUserId());
		String constatus = this.ObjectToStringDB(news.getContentStatus());
		Integer area_province = news.getArea_province();
		Integer area_city = news.getArea_city();
		Integer area_county = news.getArea_county();
		
		Integer area_street = news.getArea_street();
		String address = this.ObjectToStringDB(news.getAddress());
		String phone = this.ObjectToStringDB(news.getPhone());
		String price = this.ObjectToStringDB(news.getPrice());
		String trade_time = this.ObjectToStringDB(news.getTrade_time());
		Integer park = news.getPark();
		Integer card = news.getCard();
		Integer isCommentAudit = news.getIsCommentAudit();
		sql = "insert into t_hdcontents (contentid,contenttitle,contentsummary,category,area,area_province,area_city,area_county,area_street,note,keyword,iconurl,address,phone,price,trade_time,park,card,isCommentAudit,html," +
				"user_id,content_status) values ("+contentid+","+contentname+","+contentsum+","+
				cate+","+area+","+area_province+","+area_city+","+area_county+","+area_street+","+note+","+keyword+","+iconurl+","+address+","+phone+","+price+","+trade_time+","+park+","+card+","+isCommentAudit+","+html+","+userid+","+constatus+")";
		em.createNativeQuery(sql).executeUpdate();
		
		String contenticon = SysProperty.getPrep("sysiconid_title");
		if(contenticon == null || contenticon.equals(""))
        	contenticon = "1";
		NewsContenttitle contenttitle=new NewsContenttitle();
		contenttitle.setContentid(news.getContentId());
		contenttitle.setContenttitle(news.getContentName());
		contenttitle.setContenticon(Integer.valueOf(contenticon));
		contenttitle.setNewstype(news.getNewstype());
		contenttitle.setWeburl(news.getWeburl());
		em.persist(contenttitle);
		cid=contenttitle.getCid()+"";
		
		//hdnews在news_extends_channel_title表中新建状态是8  审核成功为9
		String newChSql="insert into news_extends_channel_title(channleid,cid,contentstatus,istest,isnew)VALUES(";
		String[] chIds = news.getChannelId().split(",");
		//栏目和内容的关系
		int channleid=0;
		for (int i = 0; i < chIds.length; i++) {
			sql="select channleid from news_extends_channel where channel_id="+chIds[i]+"";
			channleid=this.getOneInt();
			
			sql=newChSql+channleid+","+cid+",8,1,1)";
			em.createNativeQuery(sql).executeUpdate();
		}
		
		//add t_hdtags_hdcontents
		if(news.getTids()!=null&&!"".equals(news.getTids())){
			String[] tid_strList=news.getTids().split(",");
			for(int i=0;i<tid_strList.length;i++)
	        {
				int tid = Integer.parseInt(tid_strList [i]) ;
				sql = "insert into t_hdtags_hdcontents(tid,cid,tc_order) values("+tid+","+cid+",(select ifnull(max(thh.tc_order),0)+1 from (select tid,tc_order from t_hdtags_hdcontents) thh where thh.tid="+tid+"))";
				em.createNativeQuery(sql).executeUpdate();
	        }
		}
		
		return cid;
	}

	public boolean updateHdNews(NewsVo news) {
		// 更新contenttitle，where cid=？
		// 更新t_content where contentid=?
		ArrayList<String> sqlList = new ArrayList<String>();
		String title = this.ObjectToStringDB(news.getContentName());
		String summary= this.ObjectToStringDB(news.getContentSummary());
		String newstype = this.ObjectToStringDB(news.getNewstype());
		
		String category = this.ObjectToStringDB(news.getCategory());
		String area = this.ObjectToStringDB(news.getArea());
		String note = this.ObjectToStringDB(news.getNote());
		String keyword = this.ObjectToStringDB(news.getKeyword());
		String iconurl = this.ObjectToStringDB(news.getIconurl());
		String html = this.ObjectToStringDB(news.getContentHtml());
		String contentid = this.ObjectToStringDB(news.getContentId());
		Integer cid = news.getCid();
		Integer area_province = news.getArea_province();
		Integer area_city = news.getArea_city();
		Integer area_county = news.getArea_county();
		String content_status = this.ObjectToStringDB(news.getContentStatus());
		
		Integer area_street = news.getArea_street();
		String address = this.ObjectToStringDB(news.getAddress());
		String phone = this.ObjectToStringDB(news.getPhone());
		String price = this.ObjectToStringDB(news.getPrice());
		String trade_time = this.ObjectToStringDB(news.getTrade_time());
		Integer park = news.getPark();
		Integer card = news.getCard();
		Integer isCommentAudit = news.getIsCommentAudit();
		String[] chIds = news.getChannelId().split(",");
		//update t_hdcontents
		sql = "update t_hdcontents set contenttitle="+title+",contentsummary="+summary+",category="+category+",area="+area +
		",area_province="+area_province+",area_city="+area_city+",area_county="+area_county+",note="+note+",keyword="+keyword+",content_status="+content_status+",iconurl=" +iconurl+",html=" +html +
		",area_street="+area_street+",address="+address+",phone="+phone+",price="+price+",trade_time="+trade_time+",park="+park+",card="+card+",isCommentAudit="+isCommentAudit+" where contentid="+contentid;
		em.createNativeQuery(sql).executeUpdate();
		//update t_hdtags_hdcontents
		sql = "delete from t_hdtags_hdcontents where cid="+cid;
		em.createNativeQuery(sql).executeUpdate();
		if(news.getTids()!=null&&!"".equals(news.getTids())){
			String[] tid_strList=news.getTids().split(",");
			for(int i=0;i<tid_strList.length;i++)
	        {
				int tid = Integer.parseInt(tid_strList [i]) ;
				sql = "insert into t_hdtags_hdcontents(tid,cid,tc_order) values("+tid+","+cid+",(select ifnull(max(thh.tc_order),0)+1 from (select tid,tc_order from t_hdtags_hdcontents) thh where thh.tid="+tid+"))";
				em.createNativeQuery(sql).executeUpdate();
	        }
		}
		
		//update news_contenttitle
		sql = "update news_contenttitle set contenttitle="+title+",updatetime=now() where cid="+cid;
		em.createNativeQuery(sql).executeUpdate();
		//delete news_extends_channel_title
		sql = "delete from news_extends_channel_title where cid="+cid;
		em.createNativeQuery(sql).executeUpdate();
		String newChSql="insert into news_extends_channel_title(channleid,cid,contentstatus,istest,isnew)VALUES(";
		
		int channleid=0;
		for (int i = 0; i < chIds.length; i++) {
			sql="select channleid from news_extends_channel where channel_id="+chIds[i]+"";
			channleid=this.getOneInt();
			
			sql=newChSql+channleid+","+cid+",8,1,1)";
			em.createNativeQuery(sql).executeUpdate();
		}
		
		return true;
	}
	
	public void addHdNewsResource(NewsResources newsResources, NewsVo news) {
			sql = "insert into news_resources (name,resourcetype,url,size,rformat,spcode,isusebysys,newformat,fullpath) " +
    	"values ('"+newsResources.getName()+"',"+newsResources.getResourcetype()+",'"+newsResources.getUrl()+"',"+
    	newsResources.getSize()+",'"+newsResources.getRformat()+"','"+newsResources.getSpcode()+"',"+newsResources.getIsusebysys()+",'"+
    	newsResources.getNewformat()+"','"+newsResources.getFullpath()+"')";
    	em.createNativeQuery(sql).executeUpdate();
    		sql = "insert into news_contenresource (rid,cid) values ((select LAST_INSERT_ID()),"+news.getCid()+")";
		em.createNativeQuery(sql).executeUpdate();
		
		log.info("addHdNewsResource excute sql: "+sql);
	}
	
	public boolean deleteContentRrsource(String url) {
		sql = "SELECT rid FROM news_resources WHERE url='"+url+"'";
		List<Integer> lll =em.createNativeQuery(sql).getResultList();
		if(lll != null && lll.size()>0)
		{
			for(int i=0;i<lll.size();i++)
			{
				Integer rid=lll.get(i);
				this.sql = "DELETE FROM news_contenresource WHERE rid ="+rid;
				em.createNativeQuery(sql).executeUpdate();
			}
		}
		return true;
	}
	
	//modify 0830 preview image
	public ArrayList findResourceByNews(Integer cid)
	{
		//不取缩略图
		String findSql = "SELECT nr.rid,nr.useragents,nr.name,nr.resourcetype,nr.url,nr.size,nr.rformat,nr.distributorid,nr.spcode,nr.isusebysys,nr.newformat,nr.fullpath" +
				" FROM news_resources nr,news_contenresource ncr where ncr.cid="+cid +
				" and ncr.rid=nr.rid and nr.isusebysys!=9";
		try {
			return null;
//			return this.queryALLResultSet(findSql.toString());
		} catch (Exception e) {
			log.error(" findResourceByNews fail !!!" +e.getMessage());
			e.printStackTrace();
			return null;
		}
	}
	
	//add 0830 preview image
	public String findPreviewImgByNews(Integer cid)
	{
		//取缩略图fullpath
		String findSql = "SELECT nr.fullpath FROM news_resources nr,news_contenresource ncr where ncr.cid="+cid +
				" and ncr.rid=nr.rid and nr.isusebysys=9";
		try {
			return this.queryOne(findSql.toString());
		} catch (Exception e) {
			log.error(" findPreviewImgByNews fail !!!" +e.getMessage());
			e.printStackTrace();
			return null;
		}
	}
	
	public String getExistFilename(Integer cid){
		String sql = "select nr.url from news_contenresource ncr, news_resources nr where ncr.cid ="+cid+" " +
				"and ncr.rid=nr.rid and nr.rformat in ('mp3','3gp','mp4','aac')";
		try {
			return this.queryOne(sql);
		} catch (Exception e) {
			log.error(" getExistFilename fail !!!" +e.getMessage());
			return null;
		}
	}
	
	public boolean deleteHdNews(String contentid) {
		/*Object cid = this.getSingleResult("select cid from NewsContenttitle where contentid='"+contentid+"'");
		try {
			ArrayList ridList = this.queryALLResultSet("select rid from news_contenresource where cid="+cid);
			String ridstr = "";
			for(int i=0;i<ridList.size();i++)
			{
				DataMap tmp = (DataMap)ridList.get(i);
				String one = (String)tmp.get("rid");
				ridstr+="'"+one+"',";
			}
			if(!"".equals(ridstr)){
				ridstr=ridstr.substring(0, ridstr.length()-1);
	    	}
			ArrayList<String> sqlList = new ArrayList<String>();
			String deleteSql = "delete from t_hdcontents where contentid ='"+contentid+"'";
			sqlList.add(deleteSql);
			deleteSql = "delete from news_extends_channel_title where cid ="+cid;
			sqlList.add(deleteSql);
			deleteSql = "insert into t_resourcedelete (url,format) (select nr.fullpath,nr.newformat from news_resources nr,news_contenresource ncr " +
			"where ncr.cid="+cid+" and ncr.rid=nr.rid)";
			sqlList.add(deleteSql);	
			deleteSql = "delete from news_contenresource where cid="+cid;
			sqlList.add(deleteSql);
			if(!"".equals(ridstr)){
				deleteSql = "delete from news_resources where rid in ("+ridstr+")";
				sqlList.add(deleteSql);
			}
			deleteSql = "delete from news_contenttitle where contentid='"+contentid+"'";
			sqlList.add(deleteSql);
			//del t_hdtags_hdcontents
			deleteSql = "delete from t_hdtags_hdcontents where cid="+cid;
			sqlList.add(deleteSql);
			return this.execTransaction(sqlList);
		} catch (Exception e) {
			log.info(" deleteHdNews fail !!!" +e.getMessage());
			e.printStackTrace();
			return false;
		}*/
		
		//20111010
		sql="select cid from news_contenttitle where contentid='"+contentid+"'";
		int cid=getOneInt();
		
		sql="delete from t_hdtags_hdcontents where cid ="+cid;
		em.createNativeQuery(sql).executeUpdate();
		
		sql="delete from t_hdcontents where contentid ='"+contentid+"'";
		em.createNativeQuery(sql).executeUpdate();
		
		sql="delete from news_extends_channel_title where cid ="+cid;
		em.createNativeQuery(sql).executeUpdate();
		
		sql="insert into t_resourcedelete (url,format) (select nr.fullpath,nr.newformat from news_resources nr,news_contenresource ncr " +
		"where ncr.cid="+cid+" and ncr.rid=nr.rid)";
		em.createNativeQuery(sql).executeUpdate();
		
		sql="select rid from news_contenresource where cid="+cid+"";
		List<Integer> ll= em.createNativeQuery(sql).getResultList();
		
		sql="delete from news_contenresource where cid="+cid;
		em.createNativeQuery(sql).executeUpdate();
		if(ll.size()>0){
			for(int i=0;i<ll.size();i++){
				sql="delete from news_resources where rid="+ll.get(i);
				em.createNativeQuery(sql).executeUpdate();
			}
		}
		
		sql="delete from news_contenttitle where cid="+cid;
		em.createNativeQuery(sql).executeUpdate();
		
		return true;
	}
	
	public boolean subAudit(NewsVo news)
	{
		String content_id_list = news.getAllChkValue();
		String[] c_id_strList=null;
		if(content_id_list!=null && !content_id_list.equals(""))
		{
		    c_id_strList=content_id_list.split(",");
        	for(String contentId:c_id_strList)
        	{
        		 sql = "update t_hdcontents set content_status='auditing' where contentid='"+contentId+"'";
        		 em.createNativeQuery(sql).executeUpdate();
        	}
        }
		
		return true;
	}
	
	public boolean udpateAuditHdNews(NewsVo news,int unpass) throws Exception{
		String content_id_list = news.getAllChkValue();
		String[] c_id_strList=null;
		if(content_id_list!=null && !content_id_list.equals(""))
		{
		    c_id_strList=content_id_list.split(",");
        	for(String contentId:c_id_strList)
        	{
        		if(unpass==1){
        			//审核不通过
        			sql = "update t_hdcontents set content_status='unpass',cause='"+news.getCause()+"' where contentid ='"+contentId+"'";
        			em.createNativeQuery(sql).executeUpdate();
        		}else{
        			sql = "update t_hdcontents set content_status='pass',cause=null where contentid='"+contentId+"'";
	        		em.createNativeQuery(sql).executeUpdate();
	        		
	        		sql = "select nr.rid,nr.spcode,nr.resourcetype,nr.url,nr.rformat,nr.fullpath,nr.isusebysys from news_resources nr," +
					"news_contenresource ncr,news_contenttitle nc where nr.resourcetype=1 and nr.rid=ncr.rid and ncr.cid=nc.cid " +
					"and nc.contentid='"+contentId+"' order by nr.rid";
	        		
	        		String rid = null;
	        		List<Object[]>resList =  em.createNativeQuery(sql).getResultList();
					if(resList.size()>0)
					{
						String rootPath = SysProperty.getPrep("uploadpath")+"/";
						
						String imageSizeStr = SysProperty.getPrep("hdnews_imagesize");
						String imageFormat = SysProperty.getPrep("hdnews_imageformat");
						String[] imageSizeAry = null;
						String[] imageFormatAry = null;
						if(imageSizeStr!=null && !imageSizeStr.equals(""))
							imageSizeAry = imageSizeStr.split(",");
						if(imageFormat == null || imageFormat.equals(""))
							imageFormat = "jpg";
						if(imageFormat != null && imageFormat.contains(","))
							imageFormatAry = imageFormat.split(",");
						Object[] tmpMap = null; 
						ImageScalee ims = new ImageScalee();
						//缩略图缩放逻辑改变
						//1取isusebysys=9的做缩放 变量willbescale保存要缩略图的map 如果有缩略图被处理 则将要处理的图片map赋值给willbescale 
						//2循环结束判断willbescale 不为null则取willbescale缩放 为null则取resList[0]缩放
						Object[] willbescale = null; 
						for(int i=0;i<resList.size();i++)
						{
							tmpMap = resList.get(i);
							String resfilename =(String)tmpMap[5];
							String isusebysys = tmpMap[6]+"";
							log.info(" -=-=Will be scale image file-=-= " +resfilename);
							if(isusebysys.equals("9"))
							{
								willbescale = tmpMap;
							}
//								String resfilename = fullpath;//rootPath+tmpMap.get("spcode")+"/"+tmpMap.get("resourcetype")+"/"+tmpMap.get("url")+"."+tmpMap.get("rformat");
							if(!"".equals(resfilename) && imageSizeAry != null)
							{
								for(String imageSize : imageSizeAry)
								{
									String screenWidHei = imageSize.split("@")[0];
									String picWidHei = imageSize.split("@")[1];
									int screenWidth = Integer.parseInt(screenWidHei.split("x")[0]);
									int screenHeight = Integer.parseInt(screenWidHei.split("x")[1]);
									int width = Integer.parseInt(picWidHei.split("x")[0]);
									int height = Integer.parseInt(picWidHei.split("x")[1]);
									if(imageFormatAry != null)
										for(String imageFormatStr : imageFormatAry)
											ims.compressPicsHd(resfilename, width, height, imageFormatStr,screenWidth,screenHeight);
									else
										ims.compressPicsHd(resfilename, width, height, imageFormat,screenWidth,screenHeight);
								}
							}
						}
						//循环结束根据willbescale判断缩略图
						if(willbescale == null || willbescale.length==0)
							tmpMap = resList.get(0);
						else
							tmpMap = willbescale;
						rid =resList.get(0)[0]+"";
						String fullpath = (String)tmpMap[5];
						String imageSizeScaleStr = SysProperty.getPrep("hdnews_imagesizescale");
			        	String imageFormatScaleStr = SysProperty.getPrep("hdnews_imageformatscale");
			        	if(imageFormatScaleStr == null || imageFormatScaleStr.equals(""))
			        		imageFormatScaleStr = "jpg";
						String[] imageSizeScaleAry = imageSizeScaleStr.split(","); 
						String[] imageFormatScaleAry = imageFormatScaleStr.split(","); 
						if(imageSizeScaleAry != null&&imageSizeScaleAry.length>0)
						{
							for(String imageSizeScale:imageSizeScaleAry)
							{
								String screenWidHei = imageSizeScale.split("@")[0];
								String picWidHei = imageSizeScale.split("@")[1];
								int screenWidth = Integer.parseInt(screenWidHei.split("x")[0]);
								int screenHeight = Integer.parseInt(screenWidHei.split("x")[1]);
								int width = Integer.parseInt(picWidHei.split("x")[0]);
								int height = Integer.parseInt(picWidHei.split("x")[1]);
								if(imageFormatScaleAry!=null && imageFormatScaleAry.length>0)
									for(String imageFormatScale:imageFormatScaleAry)
										ims.compressPicsScaleHd(fullpath, width, height, imageFormatScale, screenWidth, screenHeight);
							}
						}
						String scaleFullPath = fullpath.replace(".", "-p.");
						sql = "insert into news_resources (name,newformat,fullpath) values('"+rid+"','"+imageFormatScaleStr+"','"+scaleFullPath+"')";
						em.createNativeQuery(sql).executeUpdate();
						//把上传根路径替换为imgportal路径,更新t_hdcontents中的iconurl
						String staticurl = SysProperty.getPrep("hdnews_imgurl");
						scaleFullPath = scaleFullPath.replaceAll(rootPath, staticurl);
						sql = "update t_hdcontents set iconurl='" +scaleFullPath+ "' where contentid='"+contentId+"'";
						em.createNativeQuery(sql).executeUpdate();
					}
	        		
					if(rid!=null){
						sql="select n.rid from news_resources n where n.name='"+rid+"'";
						int iconrid = getOneInt();
						if(iconrid>0){
							sql = "update news_contenttitle set contenticon="+iconrid+" where contentid='"+contentId+"'";
		                	em.createNativeQuery(sql).executeUpdate();
						}
			        }else{
			        	String contenticon = SysProperty.getPrep("sysiconid_title");
	        			if(contenticon == null || contenticon.equals(""))
	        	        	contenticon = "1";
	        			sql = "update news_contenttitle set contenticon='"+contenticon+"' where contentid='"+contentId+"'";
	                	em.createNativeQuery(sql).executeUpdate();
			        }
					
	    			//修改内容在栏目表的状态,硬件资讯9为通过审核
	        		sql="select cid from news_contenttitle where contentid='"+contentId+"'";
    				int cid = getOneInt();
	    			sql = "update news_extends_channel_title set contentstatus=9 where cid=" + cid;
	    			em.createNativeQuery(sql).executeUpdate();
    				//todo执行生成静态网页
    				NewsVo newsvo = this.getHdNewsByCOntentid(contentId);
    				Executor executor = Executors.newSingleThreadExecutor();
    				Map<String, String> m=getUrl_NewformatByContentid(contentId);
    				executor.execute(new GenerateNewsHtml(newsvo,HDNEWS_TYPE,m));
        		}
        		
        	}
	    }
		
		return true;
		
	}
	
	public boolean disableHdNews(String contentIdStr)
	{
		ArrayList<String> sqlList = new ArrayList<String>();
		sql = "update t_hdcontents set content_status='disable' where contentid in ("+contentIdStr+")";
		sqlList.add(sql);
		String cidstr = this.getCidListByContentid(contentIdStr);
		sql = "update news_extends_channel_title set contentstatus=10 where cid in (" + cidstr +")";
		sqlList.add(sql);
		try {
			return execTransaction(sqlList);
		} catch (Exception e) {
			log.error(" disableHdNews fail !!!" +e.getMessage());
			e.printStackTrace();
			return false;
		}
	}
	
	public String getCidListByContentid(String contentIdStr)
	{
		String findSql = "SELECT cid FROM news_contenttitle where contentid in ("+contentIdStr+")";
		String result = "";
		try {
			List<Integer> temp=this.em.createNativeQuery(sql).getResultList();
			for(Integer o:temp)
			{
			//	String cid = (String)tmp.get("cid");
				result += o.toString()+",";
			}
			if(!"".equals(result))
				result = result.substring(0,result.length()-1);
		} catch (Exception e) {
			logger.error(" getCidListByContentid fail !!!" +e.getMessage());
		}
		return result;
	}
	
	public ArrayList<DataMap> findSelectChannelsByCid(Integer cid)
	{
		String findSql = "select   ch.channel_id  ,ch.service_code,ch.channel_name,ch.channel_comment ,ch.function_name,ch.STATUS ,ch.ordered ,ch.created_date" +
		",ch.previous_id ,ch.primal_id ,ch.LEVEL ,ch.channel_type  ,ch.channel_hot_topics ,ch.channel_classifying_label" +"" +
				" from t_channels tc,news_extends_channel nec,news_extends_channel_title nect " +
				"where nect.cid="+cid+" and nect.channleid=nec.channleid and nec.channel_id=tc.channel_id";
		try {
			return null;
			//return this.queryALLResultSet(findSql.toString());
		} catch (Exception e) {
			logger.error(" findAllLastChannel fail !!!" +e.getMessage());
			return null;
		}
	}

	public ArrayList<DataMap> findAllLastChannel(DataMap hashMap) {
		Object roleId = hashMap.get("role_id");
		Object userId = hashMap.get("user_id");
		String userids = (String)hashMap.get("userids");
		//root
		String findSql =  "select   ch.channel_id  ,ch.service_code,ch.channel_name,ch.channel_comment ,ch.function_name,ch.STATUS ,ch.ordered ,ch.created_date" +
		",ch.previous_id ,ch.primal_id ,ch.LEVEL ,ch.channel_type  ,ch.channel_hot_topics ,ch.channel_classifying_label" +" from t_channels ch WHERE 1=1 " +
				//will open
				" and channel_type='"+BookConfig.channel_type_hdnews+"' " + 
				" AND (function_name='"+BookConfig.channel_last+"' OR function_name='"+BookConfig.channel_song+"') ";
		//normal user
		if(roleId != null && !roleId.equals(""))
		{
			findSql = "select  ch.channel_id  ,ch.service_code,ch.channel_name,ch.channel_comment ,ch.function_name,ch.STATUS ,ch.ordered ,ch.created_date" +
				",ch.previous_id ,ch.primal_id ,ch.LEVEL ,ch.channel_type  ,ch.channel_hot_topics ,ch.channel_classifying_label" +" from t_channels ch,t_user_channels uc WHERE ch.channel_id=uc.channel_id " +
					" and channel_type='"+BookConfig.channel_type_hdnews+"' " + 
		    		" and (function_name='"+BookConfig.channel_last+"' OR function_name='"+BookConfig.channel_song+"')  " +
		    		" and uc.employee_id=(SELECT employee_id FROM t_user where user_id='" + userId + "')";
		}else if(userids!=null){
			findSql = "select distinct  ch.channel_id  ,ch.service_code,ch.channel_name,ch.channel_comment ,ch.function_name,ch.STATUS ,ch.ordered ,ch.created_date" +
				",ch.previous_id ,ch.primal_id ,ch.LEVEL ,ch.channel_type  ,ch.channel_hot_topics ,ch.channel_classifying_label" +" from t_channels ch,t_user_channels uc WHERE ch.channel_id=uc.channel_id " +
			" and channel_type='"+BookConfig.channel_type_hdnews+"' " + 
    		" and (function_name='"+BookConfig.channel_last+"' OR function_name='"+BookConfig.channel_song+"')  " +
    		" and uc.employee_id in (SELECT employee_id FROM t_user where user_id in (" + userids + "))";
		}
		findSql+=" order by previous_id";
		try {
			return null;
			//return this.queryALLResultSet(findSql.toString());
		} catch (Exception e) {
			logger.error(" findAllLastChannel fail !!!" +e.getMessage());
			return null;
		}
	}
	
	public ArrayList<DataMap> findAllLastChannel(Object roleId, String userId) {
		//root
		String findSql = "select  ch.channel_id  ,ch.service_code,ch.channel_name,ch.channel_comment ,ch.function_name,ch.STATUS ,ch.ordered ,ch.created_date" +
				",ch.previous_id ,ch.primal_id ,ch.LEVEL ,ch.channel_type  ,ch.channel_hot_topics ,ch.channel_classifying_label" +" from t_channels ch WHERE 1=1 " +
				//will open
				" and status='"+BookConfig.channel_status_enable+"'" +
				" and channel_type='"+BookConfig.channel_type_hdnews+"' " + 
				" AND (function_name='"+BookConfig.channel_last+"' OR function_name='"+BookConfig.channel_song+"') ";
		//normal user
		if(roleId != null && !roleId.equals(""))
		{
			findSql = "select  ch.channel_id  ,ch.service_code,ch.channel_name,ch.channel_comment ,ch.function_name,ch.STATUS ,ch.ordered ,ch.created_date" +
				",ch.previous_id ,ch.primal_id ,ch.LEVEL ,ch.channel_type  ,ch.channel_hot_topics ,ch.channel_classifying_label" +" from t_channels ch,t_user_channels uc WHERE ch.channel_id=uc.channel_id " +
		    		" and status='"+BookConfig.channel_status_enable+"'" +
					" and channel_type='"+BookConfig.channel_type_hdnews+"' " + 
		    		" and (function_name='"+BookConfig.channel_last+"' OR function_name='"+BookConfig.channel_song+"')  " +
		    		" and uc.employee_id=(SELECT employee_id FROM t_user where user_id='" + userId + "')";
		}
		try {
			return null;
			//return this.queryALLResultSet(findSql.toString());
		} catch (Exception e) {
			logger.error(" findAllLastChannel fail !!!" +e.getMessage());
			return null;
		}
	}
	
	
	public ArrayList<DataMap> getAllAreaOne() {
		try {
			return null;
//			return this.queryALLResultSet("SELECT * FROM t_areainfo WHERE arealevel=1  ");
		} catch (Exception e) {
			logger.error(" getAllAreaOne fail !!!" +e.getMessage());
			return null;
		}
	}
	
	public ArrayList<DataMap> getChildArea(String areaid)
	{
		try {
			return null;
//			return this.queryALLResultSet("SELECT * FROM t_areainfo WHERE upareaid="+areaid);
		} catch (Exception e) {
			logger.error(" getChildArea fail !!!" +e.getMessage());
			return null;
		}
	}
	
	public String getAreaNameById(Integer areaid)
	{
		String sql = "SELECT areaname FROM t_areainfo WHERE areaid="+areaid;
		String areaname="";
		try {
//			DataMap result = this.queryOneRow(sql);
//			areaname = result.getMapData("areaname");
			areaname =this.getOneString();
			return areaname;
		} catch (Exception e) {
			logger.error(" getAreaNameById fail !!!" +e.getMessage());
			return null;
		}
	}
	
	/*public String getFullAreaNameById(String fullname,String areaid)
	{
		String sql = "SELECT * FROM t_areainfo WHERE areaid="+areaid;
		
		try {
			DataMap result = this.queryOneRow(sql);
			fullname = result.getMapData("areaname")  + "-" + fullname;
			if(result != null && !result.isEmpty() && !result.getMapData("upareaid").equals("0"))
			{
				fullname = getFullAreaNameById(fullname,result.getMapData("upareaid"));
			}
			return fullname;
		} catch (Exception e) {
			logger.error(" getAreaNameById fail !!!" +e.getMessage());
			return null;
		}
	}*/
	
	
	public DataMap buildLastChannelMap()
    {
    	String sql = "SELECT channel_id,channel_name,function_name,previous_id FROM t_channels t";
    	HashMap<String,String> childIdAndPreiousId = new HashMap<String,String>();
    	HashMap<String,String> idAndName = new HashMap<String,String>();
    	DataMap result = new DataMap();
    	List chList = null;
    	try {
		//	chList = queryALLResultSet(sql);
		} catch (Exception e) {
			log.info(" NewsServiceImpl3 buildChChildIdAndPreviousIdMap fail !!!" +e.getMessage());
			e.printStackTrace();
			return null;
		}
		//首先建立两个map准备数据
		for(int i=0;i<chList.size();i++)
		{
			HashMap hashMap = (HashMap)chList.get(i);
			String channel_id = (String)hashMap.get("channel_id");
			String chName = (String)hashMap.get("channel_name");
			String fnName = (String)hashMap.get("function_name");
			String previous_id = (String)hashMap.get("previous_id");
			//先将id和name装入map
			idAndName.put(channel_id, chName);
			//channel如果是normal或者first,就装入map,key是channel_id,value是
			if(fnName.equals(BookConfig.channel_first) || fnName.equals(BookConfig.channel_normal))
			{
				childIdAndPreiousId.put(channel_id, previous_id);
			}
		}
		//build map,key:channleid value:previousid+name
		for(int i=0;i<chList.size();i++)
		{
			HashMap hashMap = (HashMap)chList.get(i);
			String channel_id = (String)hashMap.get("channel_id");
			String chName = (String)hashMap.get("channel_name");
			String fnName = (String)hashMap.get("function_name");
			String previous_id = (String)hashMap.get("previous_id");
			//如果是normal或者first,就装入map,key是channel_id,value是
			if(fnName.equals(BookConfig.channel_last) || fnName.equals(BookConfig.channel_song))
			{
				String previousChName = idAndName.get(previous_id);
				chName = chName+"="+previousChName;
				chName = getValue(childIdAndPreiousId,idAndName,previous_id,chName);
				//最上一级的频道名称必然是news,把news过滤掉
				chName = chName.substring(0,chName.lastIndexOf("="));
				String[] tmp = chName.split("=");
				String value = "";
				for(int j=tmp.length-1;j>=0;j--)
				{
					value=value+tmp[j]+"--";
				}
				value = value.substring(0,value.length()-2);
				result.put(channel_id, value);
			}
		}
    	return result;
    }
	
	private String getValue(HashMap<String,String> idmap,HashMap<String,String> namemap,String key,String result)
    {
    	String parentid = idmap.get(key);
    	String parentname = namemap.get(parentid);
    	if((key != null && parentid!=null)&& !parentid.equals(key))
    	{
    		result = result+"="+parentname;
    		return getValue(idmap,namemap,parentid,result);
    	}
    	else
    	{
    		return result;
    	}
    }
	
//	private String ObjectToStringDB(Object obj) {
//        try {
//            String str = obj.toString().trim();
//
//            if (str.equals("") || str.equals("null")) {
//                return "null";
//            }
//
//            return "'" + str + "'";
//        } catch (Exception e) {
//            return "null";
//        }
//    }

	/**
	 * 分类信息根栏目下一级栏目
	 * @param hdnews_rootch
	 * @return
	 */
	public List<TChannels> findfristHdchannels(String hdnews_rootch) {
		StringBuffer sqlS = new StringBuffer("SELECT t FROM TChannels t WHERE 1=1 and previous_id=:hdnews_rootch " +
				"AND channelType='"+BookConfig.channel_type_hdnews+"' and channelClassifyingLabel is not null");
		List<TChannels> queryList=new ArrayList<TChannels>();
		try {
			Query query = em.createQuery(sqlS.toString());
			query.setParameter("hdnews_rootch", hdnews_rootch);
			queryList = query.getResultList();
		} catch (Exception e) {
			logger.error(e.getMessage());
			e.printStackTrace();
		}
		return queryList;
	}

	public List<THdTagsHdContents> findAllHdTagsContentEntity(Integer cid) {
		StringBuffer sqlS = new StringBuffer("SELECT t FROM THdTagsHdContents t WHERE 1=1 and tHdTagsHdContentsPK.content.cid=:cid");
		
		List<THdTagsHdContents> queryList=new ArrayList<THdTagsHdContents>();
		try {
			Query query = em.createQuery(sqlS.toString());
			query.setParameter("cid", cid);
			queryList = query.getResultList();
			
		} catch (Exception e) {
			logger.error(e.getMessage());
			e.printStackTrace();
		}
		return queryList;
	}
	
	public List<THdTags> findAllHdTagsEntity(
			String hd1channelid) {
		StringBuffer sqlS = new StringBuffer("SELECT t FROM THdTags t WHERE 1=1 and channel.channelId=:hd1channelid");
		List<THdTags> queryList=new ArrayList<THdTags>();
		try {
			Query query = em.createQuery(sqlS.toString());
			query.setParameter("hd1channelid", hd1channelid);
			queryList = query.getResultList();
			
		} catch (Exception e) {
			logger.error(e.getMessage());
			e.printStackTrace();
		}
		return queryList;
	}

	public List<TAreainfo> findAllAreainfo(int upareaid) {
		List<TAreainfo> queryList=new ArrayList<TAreainfo>();
    	try {
	    	StringBuffer sqlS = new StringBuffer("SELECT p FROM TAreainfo p WHERE p.upareaid=:upareaid and p.areaid!=0");
	    	sqlS.append(" order by p.areaid");
	        Query query = em.createQuery(sqlS.toString());      
	        query.setParameter("upareaid", upareaid);
	        queryList=query.getResultList();  
    	} catch (Exception e) {
			e.printStackTrace();
		}
        return queryList;
	}

	/**
	 * 分类信息，内容评论记录数
	 * @param commenttext
	 * @param contentid
	 * @param delflag
	 * @param begintimepage
	 * @param endtimepage
	 * @return
	 */
	public int findContentCommentCount(String commenttext, String contentid,int delflag,String begintimepage,String endtimepage) {
		sql = "SELECT count(nc.id) FROM news_comment nc,news_contenttitle nct where isdelete!=2 and nc.cid=nct.cid";
		if (commenttext != null && !"".equals(commenttext)) {
			sql += " and nc.commenttext like '%" + commenttext + "%'";
		}
		if (contentid != null && !"".equals(contentid)) {
			sql += " and nct.contentid='" + contentid + "'";
		}
		if (delflag != 9) {
			sql += " and nc.isdelete =" + delflag;
		}
		if(begintimepage != null && !"".equals(begintimepage)) {
			sql += " and nc.commenttime > '" + begintimepage + "'";
		}
		if(endtimepage != null && !"".equals(endtimepage)) {
			sql += " and nc.commenttime < '" + endtimepage + "'";
		}
		log.info(" findContentComment excute sql : " +sql);
		int i = 0;
		try {
			i=this.getOneInt();
			
		} catch (Exception e) {
			log.error(" findContentCommentCount fail !!!" +e.getMessage());
		}
//		finally{this.closeAll(); }
		return i;
	}

	/**
	 * 分类信息，内容评论
	 * 分页
	 * @param jpage
	 * @param commenttext
	 * @param contenttitle
	 * @param delflag
	 * @param begintimepage
	 * @param endtimepage
	 * @param firstrow
	 * @param maxrow
	 * @return
	 */
	public List<CommentContent> findContentComment(JsonPage jpage,String commenttext,String contentid,int delflag,String begintimepage,String endtimepage,
			int firstrow, int maxrow) {
		
		sql = "SELECT nct.contenttitle,nc.id,nc.cid,nc.channleid,nc.clientid,nc.commenttext,nc.name,nc.commenttime,nc.isgood,nc.isdelete,nc.deleter,nc.deletetime,nc.auditer,nc.audittime" +
				" FROM news_comment nc,news_contenttitle nct where isdelete!=2 and nc.cid=nct.cid";
		if (commenttext != null && !"".equals(commenttext)) {
			sql += " and nc.commenttext like '%" + commenttext + "%'";
		}
		if (contentid != null && !"".equals(contentid)) {
			sql += " and nct.contentid='" + contentid + "'";
		}
		if (delflag !=9) {	//全部
			sql += " and nc.isdelete =" + delflag;
		}
		if(begintimepage != null && !"".equals(begintimepage)) {
			sql += " and nc.commenttime > '" + begintimepage + "'";
		}
		if(endtimepage != null && !"".equals(endtimepage)) {
			sql += " and nc.commenttime < '" + endtimepage + "'";
		}
		if(jpage!=null){
			if(jpage.getSidx()!=null&&!"".equals(jpage.getSidx())){
				if("contentname".equals(jpage.getSidx())){
					sql+=" order by nct.contenttitle";
				}else{
					sql+=" order by nc."+jpage.getSidx();
				}
			    sql+=" "+jpage.getSord();
			    log.info("jpage.getSord()返回的值是"+jpage.getSord());
			}
		}
		sql += " limit " +firstrow+","+maxrow;
		log.info(" findContentComment excute sql : " +sql);
		List result = new ArrayList();;
		try {
			SimpleDateFormat   theDate   =   new   SimpleDateFormat("yyyy-MM-dd HH:mm:ss");  
	  		
			List<Object[]> temp=this.em.createNativeQuery(sql).getResultList();
			for(Object[]o:temp)
			{
				int j = 0;
				CommentContent cc = new CommentContent();
				cc.setContentname(this.getString(o[j++]));
				cc.setId(this.getInteger(o[j++]));
				cc.setCid(this.getInteger(o[j++]));
				cc.setChannleid(this.getInteger(o[j++]));
				cc.setClientid(this.getString(o[j++]));
				cc.setCommenttext(this.getString(o[j++]));
				cc.setName(this.getString(o[j++]));
				//Date t = new Date(rs1.getTimestamp(j++).getTime());
				String ts=this.getString(o[j++]);
				if(!"".equals(ts))
				{
					//cc.setCommenttime(theDate.parse(ts));
				}
				
				cc.setIsgood(this.getInteger(o[j++]));
				cc.setIsdelete(this.getInteger(o[j++]));
				cc.setDeleter(this.getString(o[j++]));
//				Timestamp ts = rs1.getTimestamp(j++);
				 ts=this.getString(o[j++]);
				if(!"".equals(ts))
				{
					cc.setDeletetime(theDate.parse(ts));
				}
				cc.setAuditer(this.getString(o[j++]));
				
				 ts=this.getString(o[j++]);
					if(!"".equals(ts))
					{
						cc.setAudittime(theDate.parse(ts));
					}
				result.add(cc);
			}
//			this.closeAll();
		} catch (Exception e) {
			log.error(" findContentComment fail !!!" +e.getMessage());
		}
//		finally{this.closeAll(); }
		return result;
	}

	/**
	 * 删除评论信息
	 * @param sid
	 * @return boolean
	 */
	public boolean deleteAllNewsCommentByIds(String ids,String user) {
		Boolean flag=false;
		try {
			//this.init();
			String notids = getFirstColumnStringsBySql("SELECT id FROM news_comment WHERE (isdelete IS NULL OR isdelete<>2) AND id IN ("+ids+")");	//未被删除的
			
//			String notids = this.getOneString("SELECT id FROM news_comment WHERE (isdelete IS NULL OR isdelete<>2) AND id IN ("+ids+")");	//未被删除的
			if(notids!=null && !"".equals(notids)){
				this.sql="update news_comment set deletetime=now(),isdelete = 2,deleter='"+user+"' where id in ("+notids.substring(0,notids.length()-1)+")";
				//stmt.executeUpdate(sql);
				this.em.createNativeQuery(sql).executeUpdate();
			}
			flag = true;
		} catch (Exception e) {
			logger.error("deleteDeviceExtendById fail !!!" + e.getMessage());
		}
		return flag;
	}
	
	//根据sql查出第一列，并以","分隔。
	private String getFirstColumnStringsBySql(String sql){
		String cids = "";
		try {
			List<Object[]> temp=this.em.createNativeQuery(sql).getResultList();
			for(Object[]o:temp)
			{
				String value = this.getString(o[0]);
				cids += value+",";
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return cids;
	}

	/**
	 * 评论 审核通过
	 * @param ids
	 * @param user
	 * @return
	 */
	public boolean auditAllNewsComment(String ids, String user)
	{
		boolean flag = false;
		try {
		
			String notids = getFirstColumnStringsBySql("SELECT id FROM news_comment WHERE (isdelete IS NULL OR isdelete<>3) AND id IN ("+ids+")");	//审核未通过的
			if(notids!=null && !"".equals(notids)){
				String sql = "update news_comment set audittime=now(),isdelete = 3,auditer='"+user+"' where id in (" + notids.substring(0,notids.length()-1)+")";
				log.info(" auditAllNewsComment excute sql : " +sql);
				this.em.createNativeQuery(sql).executeUpdate();
			}
			flag = true;
		} catch (Exception e) {
			log.info(" auditAllNewsComment fail !!!" +e.getMessage());
			e.printStackTrace();
		} 
		return flag;
	}
	
	public boolean updateNewsContenttitle_htmlToUrl(NewsVo news) {
		try {
			sql="update news_contenttitle set contenthtml='"+news.getContentHtml()+"',weburl='"+news.getWeburl()+"' where cid='"+news.getCid()+"'";
			em.createNativeQuery(sql).executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
			return false;
		} 
		return true;
	}
	
	//得到内容的url-newformatMap映射，处理base64图片时候用
	public Map<String, String> getUrl_NewformatByContentid(String contentId) {
		 sql="select nr.url,nr.newformat from news_resources nr,news_contenresource ncr,news_contenttitle nc where nr.rid=ncr.rid and ncr.cid=nc.cid and nc.contentid=:contentId";
		 Map<String, String> m=new HashMap<String, String>();
		 List<Object[]> ll=em.createNativeQuery(sql).setParameter("contentId", contentId).getResultList();
		 if(ll.size()>0){
			 for(int i=0;i<ll.size();i++){
				 m.put((String)ll.get(i)[0], (String)ll.get(i)[1]);
			 }
		 }
    	 return m;                                      
	}
	
	//not use
//	public ArrayList getChannelPlatform()
//    {
//    	ArrayList result = new ArrayList();
//    	//1 取得所有生效的catalog对应的catalogchannelid
//    	String sql = "SELECT ncc.channelid,ncp.* FROM news_catlog nc left join news_catlog_platform ncp on ncp.catlogid=nc.catlogid," +
//    			"news_catlog_channel ncc where nc.isnowpublish in (1,2) and nc.catlogid=ncc.catlogid";
//    	ArrayList catalogChannelidAndPlatformList = new ArrayList();
//    	try {
//    		catalogChannelidAndPlatformList = queryALLResultSet(sql);
//    		if(catalogChannelidAndPlatformList.size()>0)
//    		{
//	    		for(int i=0;i<catalogChannelidAndPlatformList.size();i++)
//	    		{
//	    			DataMap lastChannelAndPlatform = new DataMap();
//	    			HashMap hashmap = (HashMap)catalogChannelidAndPlatformList.get(i);
//	    			String catalogChannelid = (String)hashmap.get("channelid");
//	    			String platforminfo = InterUtil.rb.getString("src.gameplatform")+hashmap.get("mplatform")+"."+
//	    			InterUtil.rb.getString("src.gamedistributor")+hashmap.get("magent")+"."+InterUtil.rb.getString("src.clientsn")+
//	    			hashmap.get("mserialnum")+"."+InterUtil.rb.getString("src.languagearea")+hashmap.get("mlocale");
//	    			sql = "SELECT nc.channel_id FROM news_buttons nb,news_extends_firstchannel_buttons nfb,news_contenttype nc where " +
//	    					"nfb.channelid="+catalogChannelid+" and nc.newscontenttype=nb.buttoncontenttype and nfb.buttonid=nb.buttonid";
//	    			ArrayList tchannelIdList = queryALLResultSet(sql);
//	    			if(tchannelIdList.size()>0)
//	    			{
//	    				//首先要判断结果集里的channel是否最后一级
//	    				for(int j=0;j<tchannelIdList.size();j++)
//	    				{
//	    					DataMap tmpMap = (DataMap)tchannelIdList.get(j);
//	    					String tch_id = tmpMap.getMapData("channel_id");
//	    					if(tch_id == null || tch_id.equals(""))
//	    						continue;
//	    					sql = "select function_name from t_channels where channel_id='"+tch_id+"'";
//	    					String functionname = queryOne(sql);
//	    					//if最后一级 放到map里 key是最后一级的id value是platforminfo
//	    					if(functionname.toUpperCase().equals("LAST") || functionname.toUpperCase().equals("CHANNELSONG"))
//	    						lastChannelAndPlatform.put(tch_id, platforminfo);
//	    					//不是 递归查询
//	    					else
//	    						buildLastChannelAndPlatform(tch_id,platforminfo,lastChannelAndPlatform);
//	    				}
//	    			}
//	    			result.add(lastChannelAndPlatform);
//	    		}
//    		}
//		} catch (Exception e) {
//			log.info(" NewsServiceImpl3 getChannelPlatform fail !!!" +e.getMessage());
//			e.printStackTrace();
//		}
//    	return result;
//    }
//	//not use
//	private void buildLastChannelAndPlatform(String tch_id,String platforminfo,DataMap lastChannelAndPlatform)
//    {
//    	String sql = "SELECT channel_id,function_name FROM t_channels where previous_id='"+tch_id+"'";
//    	try {
//			ArrayList chList = queryALLResultSet(sql);
//			for(int i=0;i<chList.size();i++)
//			{
//				HashMap hashMap = (HashMap)chList.get(i);
//				String fnName = (String)hashMap.get("function_name");
//				String channel_id = (String)hashMap.get("channel_id");
//				if(fnName.toUpperCase().equals("LAST") || fnName.toUpperCase().equals("CHANNELSONG"))
//					lastChannelAndPlatform.put(channel_id, platforminfo);
//				else
//					buildLastChannelAndPlatform(channel_id,platforminfo,lastChannelAndPlatform);
//			}
//		} catch (Exception e) {
//			log.info(" NewsServiceImpl3 buildLastChannelAndPlatform fail !!!" +e.getMessage());
//			e.printStackTrace();
//		}
//    }
}
